using System;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Xml;
using System.Web;
using System.Web.Services;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.ApplicationBlocks.ExceptionManagement;
using Dimok.Data.Database;
using Dimok;
using Dimok.Concurrency;
using Dimok.Data;
using Dimok.Exceptions;

namespace Dimok.Menu.WS
{
	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Summary description for Lists. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public class Lists : System.Web.Services.WebService{

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Where ds ex. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected class WhereDSEx : WhereDS{
			public WhereDSEx(XmlElement xmlDS){
				if( xmlDS != null && xmlDS.ChildNodes.Count > 0 )
					ReadXmlSerializable(new XmlNodeReader(xmlDS));
			}
		}
		protected UserObjectEx uox = new UserObjectEx();

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Default constructor. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public Lists(){
			//CODEGEN: This call is required by the ASP.NET Web Services Designer
			InitializeComponent();
		}

		#region Component Designer generated code
		
		//Required by the Web Services Designer 
		private IContainer components = null;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	
		/// Required method for Designer support - do not modify the contents of this method with the code
		/// editor. 
		/// </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private void InitializeComponent()
		{
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Clean up any resources being used. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="disposing">	true if resources should be disposed, false if not. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected override void Dispose( bool disposing )
		{
			if(disposing && components != null)
			{
				components.Dispose();
			}
			base.Dispose(disposing);		
		}
		
		#endregion

		#region History =================

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Histories. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		///
		/// <param name="a">	a. </param>
		/// <param name="b">	The. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public string History(string a,string b){// a - func name
			try{
				return SQLHelperEx.ADOToString(uox.dbADO,"SELECT * FROM dbo.fh_"+a+"("+uox.applIDSec+(b!=null?","+b:"")+")");
			}catch(Exception exp){throw new ASPException(exp);}
		}
		#endregion History =================

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Menu tree xml. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ClientException">	Thrown when client. </exception>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public void MenuTreeXML(){
			try{
				Context.Response.Write(XmlEx.GetLevelsXml(uox.db,"s_MenusCount","MenuID","ParentMenuID","MYTREE","TREENODE").Replace("NewDataSet","TREENODES").Replace("MYTREE:",""));
			}catch(Exception exc){ throw new ClientException(exc); }
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Locks the next applicant. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		///
		/// <param name="applID">	Identifier for the appl. </param>
		/// <param name="menuID">	Identifier for the menu. </param>
		///
		/// <returns>	true if it succeeds, false if it fails. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public bool LockNextApplicant(int applID,int menuID){
			try{
				//uox.applID = applID;	
				//if( uox.menuID != menuID )return false; // applicant is in different menu
				return !Locker.IsLocked(applID);
			}catch(Exception exp){throw new ASPException(exp);}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Saves to file. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		///
		/// <param name="text">					The text. </param>
		/// <param name="contentType">	Type of the content. </param>
		/// <param name="fileName">			Filename of the file. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public void SaveToFile(string text,string contentType,string fileName){
			try{
				Context.Response.ContentType = contentType;//"application/x-msexcel";
				Context.Response.AddHeader("content-disposition","attachment; filename=\""+fileName+"\"");
				Context.Response.Write(text);
			}catch(Exception exp){throw new ASPException(exp);}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Shows the sql. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ClientException">	Thrown when client. </exception>
		///
		/// <param name="a">	a. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public string ShowSQL(string a){
			if( a.ToUpper().IndexOf("SELECT ") != 0 )a = "SELECT * FROM "+a;
			string objectName = Regex.Match(a,@"\sFROM\s+(\S+)",RegexOptions.IgnoreCase).Groups[1].Value;
			try{
				if( (int)SQLHelperEx.ExecuteScalarEx(uox.db,"SELECT count(*) FROM dbo.RegistryValues('ShowSQL') WHERE Value=@View",objectName) == 0 )
					throw new ClientException("Object "+objectName+" is not registered.");
				return SQLHelperEx.ADOToString(uox.dbADO,a);
			}catch(ClientException){
				throw;
			}catch(Exception exc){
				ExceptionManager.Publish(exc);
				throw new ClientException("O-o-o-ps!");
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List where. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ClientException">	Thrown when client. </exception>
		///
		/// <param name="filter">	The filter. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public string ListWhere(string filter){
			try{
				filter = SQLHelperEx.CheckInjects(filter+"").Replace("*","%");
				string sql = "sec_ListWhere";
				ADODB.RecordsetClass rsWhere = SQLHelperEx.AppendRS(uox.dbADO,60,sql,filter);
				if( rsWhere.RecordCount > 1000 )throw new ClientException(rsWhere.RecordCount + " records was found for given parameter(s).\n\nNarrow down the search criterion or else ...");
				string adoRS = SQLHelperEx.ADOToString(rsWhere);
				//string adoRS1 = SQLHelperEx.ADOToString(uox.dbADO,"SELECT * FROM vsec_ListWhere WHERE "+filter);
				//string adoRS2 = SQLHelperEx.ADOToString(uox.dbADO,"SELECT * FROM  th_ADOTypes");
				return adoRS;
			}catch(Exception exp){throw new ClientException(exp);}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List where fast. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ClientException">	Thrown when client. </exception>
		///
		/// <param name="filter">	The filter. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[WebMethod]
		public string ListWhereFast(XmlElement filter){
			try{
				const string SP_WHERE = "SELECT * FROM vsec_ListWhere WHERE ";
				WhereDSEx dsWhere = new WhereDSEx(filter);
				WhereDS.WhereDataTable dtWhere = dsWhere.Where;
				StringList where = new StringList();
				ArrayList parameters = new ArrayList();
				if( dtWhere != null )
					foreach( WhereDSEx.WhereRow row in dtWhere.Select("","FieldName ASC") ){
						where.Add(SQLHelperEx.CheckInjects(row.FieldName)+" "+SQLHelperEx.CheckInjects(row.Operand)+" ?");
						parameters.Add(Regex.Replace(Regex.Replace(row.Value,"^[*]","%"),"[*]$","%"));
					}
				string listWhereSQL = SP_WHERE+(where.Count>0 ? where.Join(" AND\n") : Config.db.ApplID+"=0");
				Debug.WriteLine("listWhereSQL:\n"+listWhereSQL);
				ADODB.RecordsetClass rsWhere = SQLHelperEx.AppendRS(uox.dbADO,listWhereSQL,parameters.ToArray());
				if( rsWhere.RecordCount > 1000 )throw new ClientException(rsWhere.RecordCount + " records was found for given parameter(s).\n\nNarrow down the search criterion or else ...");
				return SQLHelperEx.ADOToString(rsWhere);
			}catch(Exception exp){throw new ClientException(exp);}	
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List where sql. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlFilter">	Element describing the xml filter. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public object[] ListWhereSQL(XmlElement xmlFilter){
			string filter = "1=0";
			ArrayList alWhere = new ArrayList();
			ArrayList alParams = new ArrayList();
			if( xmlFilter != null ){
				DataColumnCollection cols0 = SQLHelperEx.ExecuteDatasetEx(uox.db,"SELECT TOP 0 * FROM vsec_ListWhere").Tables[0].Columns;
				DataRow row = SQLHelperEx.LoadDataSet(xmlFilter).Tables[0].Rows[0];
				DataColumnCollection cols = row.Table.Columns;
				object[] oRet = new object[2];

				foreach( DataColumn col in row.Table.Columns )
					if( row[col] != null && row[col].ToString() != "" ){
						alWhere.Add(col.ColumnName + " "+(row[col].GetType().IsPrimitive?"=":"LIKE")+" ?");
						alParams.Add(MakeFilterParamValue(row[col],cols0[col.ColumnName]));
					}
				filter = string.Join(" AND ",(string[])alWhere.ToArray(typeof(string)));
			}
			return new object[]{"SELECT * FROM vsec_ListWhere WHERE "+(filter == "" ? "1=0" : filter),alParams.ToArray()};
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Makes a filter parameter value. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="param">	The parameter. </param>
		/// <param name="col">		The col. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private object MakeFilterParamValue(object param,DataColumn col){
			return col.DataType.IsPrimitive ? param : '%'+param.ToString()+'%'; 
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List menu. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ClientException">	Thrown when client. </exception>
		///
		/// <param name="connADO">	The connection a do. </param>
		/// <param name="menuID">		Identifier for the menu. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string ListMenu(string connADO,int menuID){
			string sql = Config.Menu.ExecuteSQL == "" ? "SELECT OnClick FROM t_Menu WHERE MenuID = @MenuID" : Dimok.Config.Menu.ExecuteSQL;
			sql = SQLHelperEx.ExecuteScalarEx(uox.db,sql,menuID)+"";
			try{
				return SQLHelperEx.ADOToString(connADO,120,sql.Replace("~menuid~",menuID+""));
			}catch(Exception exc){ throw new ClientException(exc,(NameValueCollection)new NameValueCollectionEx("SQL",sql)); }
		}
	}
}
